package com.quick.mybatis.sqlprovider;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.ParameterizedType;
import java.text.MessageFormat;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.quick.mybatis.annotation.ClassMetaEnum;
import com.quick.mybatis.annotation.Column;
import com.quick.mybatis.annotation.FieldMetaEnum;
import com.quick.mybatis.annotation.Id;
import com.quick.mybatis.annotation.OrderBy;
import com.quick.mybatis.annotation.OrderType;
import com.quick.mybatis.annotation.Relevance;
import com.quick.mybatis.annotation.Table;
import com.quick.mybatis.model.PageModel;
import com.quick.mybatis.service.impl.ClassContext;
import com.quick.mybatis.utils.ObjectUtil;
import com.quick.mybatis.utils.StringUtil;

@SuppressWarnings("unchecked")
public class CommonProvider<T> {
	
	private static final Log log = LogFactory.getLog(CommonProvider.class);
	
	public final String AND = " AND";

	public String getUpdateSql(Object t) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		Map<ClassMetaEnum, Object> classMap = ObjectUtil.getClassMaps(t.getClass());
		Table tableMeta = ((Table) classMap.get(ClassMetaEnum.C_TABLE_META));
		if (tableMeta == null) {
			throw new NullPointerException(
					" The table name annotation is empty.");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE  ");
		sql.append(tableMeta.tableName());
		sql.append(" SET ");
		Map<String, Map<FieldMetaEnum, Object>> fieldMap = (Map<String, Map<FieldMetaEnum, Object>>) classMap
				.get(ClassMetaEnum.C_FIELD);
		if (fieldMap == null || fieldMap.size() <= 0) {
			return "";
		}
		for (Entry<String, Map<FieldMetaEnum, Object>> entry : fieldMap.entrySet()) {
			final String field = entry.getKey();
			final Object value = ObjectUtil.invokeMethod(t.getClass(),
					StringUtil.getMethod(field), null, t);
			if (StringUtil.isEmpty(value)  ) {
				continue;
			}
			Map<FieldMetaEnum, Object> meta = entry.getValue();
			Column fieldMeta = (Column) meta.get(FieldMetaEnum.COLUMN);
			Id id = (Id) meta.get(FieldMetaEnum.ID);
			if ((fieldMeta !=null && !fieldMeta.where() && !fieldMeta.primaryKey()) || StringUtil.isNotEmpty(id)){
				sql.append(createUpdate(fieldMeta,entry.getKey()) + " ,");
				continue;
			}
		}

		if (sql.toString().endsWith(",")) {
			sql.delete(sql.length() - 1, sql.length());
		}
		// 修改条件
		sql.append(" WHERE ");
		for (Entry<String, Map<FieldMetaEnum, Object>> entry : fieldMap.entrySet()) {
			Map<FieldMetaEnum, Object> meta = entry.getValue();
			Object value = ObjectUtil.invokeMethod(t.getClass(),
					StringUtil.getMethod(entry.getKey()), null, t);
			Column fieldMeta = (Column) meta.get(FieldMetaEnum.COLUMN);
			Id id = (Id) meta.get(FieldMetaEnum.ID);
			if (StringUtil.isNotEmpty(value) && (fieldMeta != null && fieldMeta.primaryKey()) || StringUtil.isNotEmpty(id)) {
				sql.append(createUpdate(fieldMeta,entry.getKey()) + " AND");
			}
		}

		if (sql.toString().endsWith("AND")) {
			sql.delete(sql.length() - "AND".length(), sql.length());
		}
		if(log.isDebugEnabled()){
			log.debug("CommonProvider.getUpdateSql:"+sql.toString());
		}
		return String.valueOf(sql);
	}

	public String getInsertSql(Object t) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		Map<ClassMetaEnum, Object> classMap = ObjectUtil.getClassMaps(t.getClass());
		Table tableMeta = ((Table) classMap.get(ClassMetaEnum.C_TABLE_META));
		if (tableMeta == null) {
			throw new NullPointerException(
					" The table name annotation is empty.");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO  ");
		sql.append(tableMeta.tableName());

		Map<String, Map<FieldMetaEnum, Object>> methodMap = (Map<String, Map<FieldMetaEnum, Object>>) classMap
				.get(ClassMetaEnum.C_FIELD);
		if (methodMap == null || methodMap.size() <= 0) {
			return "";
		}
		StringBuffer columns = new StringBuffer();
		StringBuffer values = new StringBuffer();
		for (Entry<String, Map<FieldMetaEnum, Object>> entry : methodMap.entrySet()) {
			Object value = ObjectUtil.invokeMethod(t.getClass(),
					StringUtil.getMethod(entry.getKey()), null, t);
			if (StringUtil.isEmpty(value)  ) {
				continue;
			}
			Map<FieldMetaEnum, Object> meta = entry.getValue();
			Column fieldMeta = (Column) meta.get(FieldMetaEnum.COLUMN);
			columns.append(getColumnName(fieldMeta, entry.getKey()) + ","); 
			values.append(createInsert(fieldMeta,entry.getKey()) + " ,");
		}
		if (sql.toString().endsWith(",")) {
			sql.delete(sql.length() - 1, sql.length());
		}

		if (values.toString().endsWith(",")) {
			values.delete(values.length() - 1, values.length());
		}
		
		if (columns.toString().endsWith(",")) {
			columns.delete(columns.length() - 1, columns.length());
		}
		sql.append("(");
		sql.append(columns);
		sql.append(" )");
		sql.append(" VALUES (");
		sql.append(values);
		sql.append(")");
		if(log.isDebugEnabled()){
			log.debug(sql.toString());
		}
		return String.valueOf(sql);
	}

	public String getAllInsertSql(Map<String, List<T>> params) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		List<T> list = params.get("list");
		if (list == null || list.size() <=0) {
			return "";
		}
		Object t = list.get(0);
		Map<ClassMetaEnum, Object> classMap = ObjectUtil.getClassMaps(t.getClass());
		Table tableMeta = ((Table) classMap.get(ClassMetaEnum.C_TABLE_META));
		if (tableMeta == null) {
			throw new NullPointerException(
					" The table name annotation is empty.");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO  ");
		sql.append(tableMeta.tableName());

		Map<String, Map<FieldMetaEnum, Object>> methodMap = (Map<String, Map<FieldMetaEnum, Object>>) classMap
				.get(ClassMetaEnum.C_FIELD);
		if (methodMap == null || methodMap.size() <= 0) {
			return "";
		}
		StringBuffer columns = new StringBuffer();
		StringBuffer values = new StringBuffer();
		StringBuffer formatValues = new StringBuffer();

		for (Entry<String, Map<FieldMetaEnum, Object>> entry : methodMap.entrySet()) {
			final String key = entry.getKey();
			if (!isHaveValue(list, key)) {
				continue;
			}
			Map<FieldMetaEnum, Object> meta = entry.getValue();
			Column fieldMeta = (Column) meta.get(FieldMetaEnum.COLUMN);
			columns.append(fieldMeta.columnName() + ",");
			values.append(createInsertAll(fieldMeta,entry.getKey())+",");
		}
		
		if (columns.toString().endsWith(",")) {
			columns.delete(columns.length() - 1,
					columns.length());
		}
		if (values.toString().endsWith(",")) {
			values.delete(values.length() - 1, values.length());
		}

		MessageFormat mf = new MessageFormat("("+String.valueOf(values)+")");
		for (int i = 0; i < list.size(); i++) {
			formatValues.append(mf.format(new Object[] { i }));
			formatValues.append(",");
		}
		if (formatValues.toString().endsWith(",")) {
			formatValues.delete(formatValues.length() - 1,
					formatValues.length());
		}
		if (sql.toString().endsWith(",")) {
			sql.delete(sql.length() - 1, sql.length());
		}

		sql.append("(");
		sql.append(columns);
		sql.append(" )");
		sql.append(" VALUES ");
		sql.append(formatValues);
		sql.append("");
		if(log.isDebugEnabled()){
			log.debug(sql.toString());
		}
		return String.valueOf(sql);
	}
	
	private boolean isHaveValue(final List<T> list,final String key) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException{
		for(T t : list){
			Object value = ObjectUtil.invokeMethod(t.getClass(),
					StringUtil.getMethod(key), null, t);
			if(StringUtil.isNotEmpty(value)){
				return true;
			}
		}
		return false;
	}

	private String createInsertAll(
			Column fieldMeta,String fieldName) {
		StringBuffer sql = new StringBuffer();
		sql.append("'#{'");
		sql.append("list[{0}]." +getFieldName(fieldMeta, fieldName) + ",");
		sql.append("javaType=" + getJavaType(fieldMeta) + ",");
		sql.append("jdbcType=" + getJdbcType(fieldMeta));
		sql.append("}");
		return String.valueOf(sql);
	}

	private String createInsert(
			Column fieldMeta,final String fieldName) {
		StringBuffer sql = new StringBuffer();
		sql.append("#{");
		sql.append(getFieldName(fieldMeta, fieldName) + ",");
		sql.append("javaType=" + getJavaType(fieldMeta) + ",");
		sql.append("jdbcType=" + getJdbcType(fieldMeta));
		sql.append("}");
		return String.valueOf(sql);
	}

	private String createQuery(Table tableMeta,
			Column fieldMeta, Object obj,final String fieldName) {
		StringBuffer sql = new StringBuffer();
		sql.append(" ");
		sql.append(tableMeta.tableName()+".");
		sql.append(getColumnName(fieldMeta, fieldName));
		sql.append("=");
		sql.append("#{");
		if (obj instanceof PageModel) {
			sql.append("item."+getFieldName(fieldMeta, fieldName) + ",");
		} else {
			sql.append(getFieldName(fieldMeta, fieldName) + ",");
		}
		sql.append("javaType=" + getJavaType(fieldMeta) + ",");
		sql.append("jdbcType=" + getJdbcType(fieldMeta));
		sql.append("}");
		return String.valueOf(sql);
	}

	private String createUpdate(Column fieldMeta,String fieldName) {
		StringBuffer sql = new StringBuffer();
		sql.append(" ");
		sql.append(getColumnName(fieldMeta, fieldName));
		sql.append("=");
		sql.append("#{");
		sql.append(getFieldName(fieldMeta,fieldName) + ",");
		sql.append("javaType=" + getJavaType(fieldMeta)+",");
		sql.append("jdbcType=" + getJdbcType(fieldMeta));
		sql.append("}");
		return String.valueOf(sql);
	}
	
	private String getColumnName(Column fieldMeta,String fieldName){
		if(fieldMeta == null || StringUtil.isEmpty(fieldMeta.columnName())){
			return fieldName;
		}
		return fieldMeta.columnName();
	}
	
	private String getFieldName(Column fieldMeta,String fieldName){
		if(fieldMeta == null || StringUtil.isEmpty(fieldMeta.fieldName())){
			return fieldName;
		}
		return fieldMeta.columnName();
	}
	
	private String getJavaType(Column fieldMeta){
		if(fieldMeta == null){
			return "string";
		}
		return String.valueOf(fieldMeta.javaType()).toLowerCase();
	}
	
	private String getJdbcType(Column fieldMeta){
		if(fieldMeta == null){
			return "VARCHAR";
		}
		return String.valueOf(fieldMeta.jdbcType());
	}

	public String getDeleteSql(Object t) {
		Map<ClassMetaEnum, Object> classMap = ObjectUtil.getClassMaps(t.getClass());
		Table tableMeta = ((Table) classMap.get(ClassMetaEnum.C_TABLE_META));
		if (tableMeta == null) {
			throw new NullPointerException(
					" The table name annotation is empty.");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE  FROM ");
		sql.append(tableMeta.tableName());
		Map<String, Map<FieldMetaEnum, Object>> methodMap = (Map<String, Map<FieldMetaEnum, Object>>) classMap
				.get(ClassMetaEnum.C_FIELD);
		if (methodMap == null || methodMap.size() <= 0) {
			return "";
		}
		// 修改条件
		sql.append(" WHERE ");
		for (Entry<String, Map<FieldMetaEnum, Object>> entry : methodMap.entrySet()) {
			Map<FieldMetaEnum, Object> meta = entry.getValue();
			Column fieldMeta = (Column) meta.get(FieldMetaEnum.COLUMN);
			Id id = (Id) meta.get(FieldMetaEnum.ID);
			if ((fieldMeta != null && fieldMeta.primaryKey()) || StringUtil.isNotEmpty(id)) {
				sql.append(createUpdate(fieldMeta,entry.getKey()) + " AND");
			}
		}

		if (sql.toString().endsWith("AND")) {
			sql.delete(sql.length() - "AND".length(), sql.length());
		}
		if(log.isDebugEnabled()){
			log.debug(sql.toString());
		}
		return String.valueOf(sql);
	}

	public String getListSql(Object obj) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		Object t = null;
		if (obj instanceof PageModel) {
			t = ObjectUtil.invokeMethod(obj.getClass(), "getItem", null, obj);
		} else {
			t = obj;
		}
		Map<ClassMetaEnum, Object> classMap = ObjectUtil.getClassMaps(t.getClass());
		Table tableMeta = ((Table) classMap.get(ClassMetaEnum.C_TABLE_META));
		if (tableMeta == null) {
			throw new NullPointerException(
					" The table name annotation is empty.");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT "+tableMeta.tableName()+".*  FROM ");
		sql.append(tableMeta.tableName());
		sql.append(getRelevanceQuery(tableMeta,classMap,obj));
		sql.append(getListWhere(tableMeta,classMap, obj));
		sql.append(getOrderBy(tableMeta, classMap, obj));
		if(log.isDebugEnabled()){
			log.debug(sql.toString());
		}
		return String.valueOf(sql);
	}
	
	
	/*public String getInList(T t,List<String> list){
			Map<ClassMetaEnum, Object> classMap = ObjectUtil.getClassMaps(t.getClass());
			TableMeta tableMeta = ((TableMeta) classMap.get(ClassMetaEnum.C_TABLE_META));
			if (tableMeta == null) {
				throw new NullPointerException(
						" The table name annotation is empty.");
			}
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT "+tableMeta.tableName()+".*  FROM ");
			sql.append(tableMeta.tableName());
			sql.append(getRelevanceQuery(tableMeta,classMap,t));
			sql.append(getListWhere(tableMeta,classMap, t));
			sql.append(getOrderBy(tableMeta, classMap, t));
			if(log.isDebugEnabled()){
				log.debug(sql.toString());
			}
			return String.valueOf(sql);
	}
	
	private String getInWhere(TableMeta tableMeta,List<String> list){
		StringBuffer sql = new StringBuffer();
		sql.append(" WHERE 1=1 ").append(AND);
		for(){
			sql.append(tableMeta.tableName()+".")
		}
		return String.valueOf(sql);
	}*/
	
	
	private String getOrderBy(Table tableMeta ,Map<ClassMetaEnum, Object> classMap,Object obj){
		StringBuffer sql = new StringBuffer();
        Map<String, Map<FieldMetaEnum, Object>> methodMap = (Map<String, Map<FieldMetaEnum, Object>>) classMap
				.get(ClassMetaEnum.C_FIELD);
		if (methodMap == null || methodMap.size() <= 0) {
			return "";
		}
		sql.append(" ORDER BY ");
		for (Entry<String, Map<FieldMetaEnum, Object>> entry : methodMap.entrySet()) {
			Map<FieldMetaEnum, Object> meta = entry.getValue();
			if(meta.get(FieldMetaEnum.ORDER) instanceof OrderBy){
				OrderBy orderByMeta = (OrderBy) meta.get(FieldMetaEnum.ORDER);
				sql.append(" "+orderByMeta.columnName() +" " + orderByMeta.orderType() + ",");
			}
		}
		
		if (sql.toString().endsWith(",")) {
			sql.delete(sql.length() - 1, sql.length());
		}
		
		if(sql.indexOf(OrderType.ASC.name()) <=-1 || sql.indexOf(OrderType.DESC.name()) <= -1 ){
			return "";
		}
		return String.valueOf(sql);
	}
	
	private String getRelevanceQuery(Table tableMeta ,Map<ClassMetaEnum, Object> classMap,Object obj){
		StringBuffer sql = new StringBuffer();
		Map<String, Map<FieldMetaEnum, Object>> methodMap = (Map<String, Map<FieldMetaEnum, Object>>) classMap
				.get(ClassMetaEnum.C_FIELD);
		if (methodMap == null || methodMap.size() <= 0) {
			return "";
		}
		for (Entry<String, Map<FieldMetaEnum, Object>> entry : methodMap.entrySet()) {
			Map<FieldMetaEnum, Object> meta = entry.getValue();
			if(!(meta.get(FieldMetaEnum.RELEVANCE) instanceof Relevance)){
				continue;
			}
			Relevance relevanceMeta = (Relevance) meta.get(FieldMetaEnum.RELEVANCE);
			sql.append(" "+relevanceMeta.innerType());
			sql.append(" JOIN ");
			sql.append(relevanceMeta.tableName());
			sql.append(" ON "+tableMeta.tableName() + "."+ meta.get(FieldMetaEnum.FIELD));
			sql.append(" = ");
			sql.append(relevanceMeta.tableName() + "."+ meta.get(FieldMetaEnum.COLUMN));
		}
		if (sql.toString().endsWith(AND)) {
			sql.delete(sql.length() - AND.length(), sql.length());
		}
		return String.valueOf(sql);
	}

	public String getListCountSql(Object obj) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		Object t = null;
		if (obj instanceof PageModel) {
			t = ObjectUtil.invokeMethod(obj.getClass(), "getItem", null, obj);
		} else {
			t = obj;
		}
		Map<ClassMetaEnum, Object> classMap = ObjectUtil.getClassMaps(t.getClass());
		Table tableMeta = ((Table) classMap.get(ClassMetaEnum.C_TABLE_META));
		if (tableMeta == null) {
			throw new NullPointerException(
					" The table name annotation is empty.");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(1)  FROM ");
		sql.append(tableMeta.tableName());
		sql.append(getRelevanceQuery(tableMeta,classMap,obj));
		sql.append(getListWhere(tableMeta,classMap, obj));
		if(log.isDebugEnabled()){
			log.debug(sql.toString());
		}
		return String.valueOf(sql);
	}

	private String getListWhere(Table tableMeta,Map<ClassMetaEnum, Object> classMap, Object obj) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		Object t = null;
		if (obj instanceof PageModel) {
			t = ObjectUtil.invokeMethod(obj.getClass(), "getItem", null, obj);
		} else {
			t = obj;
		}
		StringBuffer sql = new StringBuffer();
		Map<String, Map<FieldMetaEnum, Object>> methodMap = (Map<String, Map<FieldMetaEnum, Object>>) classMap
				.get(ClassMetaEnum.C_FIELD);
		if (methodMap == null || methodMap.size() <= 0) {
			return "";
		}
		sql.append(" WHERE 1=1 ").append(AND);
		for (Entry<String, Map<FieldMetaEnum, Object>> entry : methodMap.entrySet()) {
			final String key = StringUtil.getMethod(entry.getKey());
			Object value = ObjectUtil.invokeMethod(t.getClass(),
					key, null, t);
			if (StringUtil.isEmpty(value)) {
				continue;
			}
			Map<FieldMetaEnum, Object> meta = entry.getValue();
			Column fieldMeta = (Column) meta.get(FieldMetaEnum.COLUMN);
			sql.append(createQuery(tableMeta,fieldMeta, obj,entry.getKey())).append(AND);
		}
		if (sql.toString().endsWith(AND)) {
			sql.delete(sql.length() - AND.length(), sql.length());
		}
		return String.valueOf(sql);
	}

	public String getByIdSql(String id) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		Map<ClassMetaEnum, Object> classMap = ObjectUtil.getClassMaps(getEntryClass());
		Table tableMeta = ((Table) classMap.get(ClassMetaEnum.C_TABLE_META));
		if (tableMeta == null) {
			throw new NullPointerException(
					" The table name annotation is empty.");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT "+tableMeta.tableName()+".*  FROM WHERE");
		sql.append(tableMeta.tableName());
		Map<String, Map<FieldMetaEnum, Object>> methodMap = (Map<String, Map<FieldMetaEnum, Object>>) classMap
				.get(ClassMetaEnum.C_FIELD);
		for (Entry<String, Map<FieldMetaEnum, Object>> entry : methodMap.entrySet()) {
			Map<FieldMetaEnum, Object> meta = entry.getValue();
			Column fieldMeta = (Column) meta.get(FieldMetaEnum.COLUMN);
			Id id_str = (Id) meta.get(FieldMetaEnum.ID);
			if ((fieldMeta != null && fieldMeta.primaryKey()) || StringUtil.isNotEmpty(id_str)) {
				sql.append(createUpdate(fieldMeta,entry.getKey()));
			}
		}
		if(log.isDebugEnabled()){
			log.debug(sql.toString());
		}
		return String.valueOf(sql);
	}
	
	public String getSql(Object t) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		return getListSql(t);
	}

	
	public String getListAllSql(Object t) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		return getListSql(t);
	}

	public String getPageListSql(PageModel<T> t) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		StringBuffer sql = new StringBuffer();
		sql.append(getListSql(t));
		sql.append(" "+t.getWhere());
		sql.append(" limit #{startNum} ,#{pageSize} ");
		return String.valueOf(sql);
	}

	public String getPageListCountSql(PageModel<T> t) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		StringBuffer sql = new StringBuffer();
		sql.append(getListCountSql(t));
		sql.append(" "+t.getWhere());
		return String.valueOf(sql);
	}
	public String getListBySqlWhere(String sqlwhere)throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
		
		Map<ClassMetaEnum, Object> classMap = ObjectUtil.getClassMaps(getEntryClass());
		Table tableMeta = ((Table) classMap.get(ClassMetaEnum.C_TABLE_META));
		if (tableMeta == null) {
			throw new NullPointerException(
					" The table name annotation is empty.");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT "+tableMeta.tableName()+".*  FROM ");
		sql.append(tableMeta.tableName()).append(" ");
		sql.append(sqlwhere);
		if(log.isDebugEnabled()){
			log.debug(sql.toString());
		}
		return String.valueOf(sql);
	}
	
	public Class getEntryClass(){
		Map map= ClassContext.get();
		return (Class)map.get("class");
	}
}
